package com.decent.ejfadmin.report.dao;

import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.report.dao.provider.CollectCardStockReportDaoProvider;
import com.decent.ejfadmin.report.response.CollectCardStockPriceDto;
import com.decent.ejfadmin.report.response.CollectCardStockReportDto;
import com.decent.ejfadmin.report.response.EstimateStockSupplyTimeDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author weicx
 * @date 2018/08/01
 */
@Mapper
@Repository
public interface CollectCardStockReportDao {
    /**
     * 收卡卡密库存统计计数
     *
     * @param productType 运营商
     * @param face        面值
     * @return 总数
     */
    @SelectProvider(type = CollectCardStockReportDaoProvider.class, method = "collectCardStockReportCount")
    int collectCardStockReportCount(@Param("productType") Integer productType, @Param("face") String face);


    /**
     * 收卡卡密库存统计
     *
     * @param pageUnit    分页组件
     * @param productType 运营商
     * @param face        面值
     * @return 总数
     */
    @SelectProvider(type = CollectCardStockReportDaoProvider.class, method = "collectCardStockReportPage")
    List<CollectCardStockReportDto> collectCardStockReportPage(@Param("pageUnit") Page pageUnit, @Param("productType") Integer productType, @Param("face") String face);

    /**
     * 收卡库存折扣计数
     *
     * @param productType 运营商
     * @param face        面值
     * @return 总数
     */
    @SelectProvider(type = CollectCardStockReportDaoProvider.class, method = "collectCardStockPriceCount")
    int collectCardStockPriceCount(@Param("productType") Integer productType, @Param("face") String face);

    /**
     * 分页查询收卡库存折扣计
     *
     * @param pageUnit    分页组件
     * @param productType 运营商
     * @param face        面值
     * @return 总数
     */
    @SelectProvider(type = CollectCardStockReportDaoProvider.class, method = "collectCardStockPricePage")
    List<CollectCardStockPriceDto> collectCardStockPricePage(@Param("pageUnit") Page pageUnit, @Param("productType") Integer productType, @Param("face") String face);

    /**
     * 易缴费预估收卡库存销卡时间
     *
     * @param productType 运营商
     * @param face        面值
     * @return list
     */
    @Select("SELECT\n" +
            "  main.area,\n" +
            "  main.totalStockAmount,\n" +
            "  ifnull(supplied.halfHour, 0) halfHour,\n" +
            "  ifnull(supplied.oneHour, 0)  oneHour,\n" +
            "  ifnull(supplied.twoHour, 0)  twoHour\n" +
            "FROM\n" +
            "  (SELECT\n" +
            "     t1.area,\n" +
            "     t1.productType,\n" +
            "     t1.face,\n" +
            "     sum(t1.total) AS totalStockAmount\n" +
            "   FROM\n" +
            "     (SELECT\n" +
            "        sc.productType,\n" +
            "        sc.face,\n" +
            "        if(sc.supplyArea = 0, '全国', sc.area) area,\n" +
            "        count(1) AS                          total\n" +
            "      FROM yuhuang_supply_card sc\n" +
            "      WHERE sc.startTime >= date_sub(now(),interval 1 month) " +
            "AND sc.status IN (0, 3, 7, 8)\n" +
            "            AND sc.productType = #{productType} AND sc.face = #{face}\n" +
            "      GROUP BY sc.productType, sc.face, sc.supplyArea, sc.area) t1\n" +
            "   GROUP BY t1.area, t1.productType, t1.face) main\n" +
            "  LEFT JOIN\n" +
            "  (SELECT\n" +
            "     t2.area,\n" +
            "     t2.productType,\n" +
            "     t2.face,\n" +
            "     sum(t2.towHour)                                                                twoHour,\n" +
            "     sum(if(t2.endTime >= date_sub(now(),interval 1 hour), towHour, 0)) oneHour,\n" +
            "     sum(if(t2.endTime >= date_sub(now(),interval 30 minute), towHour, 0))     halfHour\n" +
            "   FROM\n" +
            "     (SELECT\n" +
            "        sc2.productType,\n" +
            "        sc2.face,\n" +
            "        sc2.endTime,\n" +
            "        if(sc2.supplyArea = 0, '全国', sc2.area) area,\n" +
            "        count(1)                               towHour\n" +
            "      FROM yuhuang_supply_card sc2\n" +
            "      WHERE sc2.endTime >= date_sub(now(),interval 2 hour) \n" +
            "            AND sc2.productType = #{productType} AND sc2.face = #{face} and sc2.status = 1\n" +
            "      GROUP BY sc2.productType, sc2.face,\n" +
            "        sc2.supplyArea, sc2.area, sc2.endTime) t2\n" +
            "   GROUP BY t2.area, t2.productType, t2.face) supplied\n" +
            "    ON main.productType = supplied.productType AND main.face = supplied.face AND main.area = supplied.area\n" +
            "ORDER BY main.area")
    List<EstimateStockSupplyTimeDto> estimateStockSupplyTime(@Param("productType") Integer productType, @Param("face") String face);

    /**
     * 查询会员库存中待销卡的最低折扣
     *
     * @param account     会员编号
     * @param productType 运营商
     * @param face        面值
     * @param area        地区
     * @return
     */
    @SelectProvider(type = CollectCardStockReportDaoProvider.class, method = "getMinUserPrice")
    BigDecimal getMinUserPrice(@Param("account") String account, @Param("productType") Integer productType, @Param("face") String face, @Param("area") String area);

    /**
     * 查询库存中小于该折扣的库存数量
     *
     * @param minUserPrice 最低折扣
     * @param productType  运营商
     * @param face         面值
     * @param area         地区
     * @return
     */
    @SelectProvider(type = CollectCardStockReportDaoProvider.class, method = "getFrontCount")
    int getFrontCount(@Param("minUserPrice") BigDecimal minUserPrice,@Param("productType") Integer productType, @Param("face") String face, @Param("area") String area);
}
